package beanstao.util.store.database.impl;

import org.apache.commons.lang.StringUtils;

import beanstao.util.Dates;
import beanstao.util.Formula;
import beanstao.util.Sber;
import beanstao.util.enums.DataType;
import beanstao.util.enums.operator.Comparison;
import beanstao.util.id.Id;
import beanstao.util.log.LogHelper;
import beanstao.util.store.database.Column;
import beanstao.util.store.database.Columns;
import beanstao.util.store.database.Field;
import beanstao.util.store.database.Fields;
import beanstao.util.store.database.SqlFunc;
import beanstao.util.store.database.Where;
import beanstao.util.store.database.interfaces.ISqls;
import beanstao.util.store.domainobject.Domain;

import com.google.common.base.Strings;

public abstract class AbstractSqls implements ISqls
{
	@Override
	public String toCreateText(String storeName, Domain domain)
	{
		domain.setCreatedTime(Dates.now());
		domain.setCreator(domain.getModifior());
		domain.setModifedTime(domain.getCreatedTime());
		StringBuilder filedName = new StringBuilder();
		StringBuilder filedVale = new StringBuilder();
		for (String key : domain.keySet())
		{
			Field field = domain.getFields().get(key);
			if (null == field)
			{
				this.logWarn(String.format("%s 未在 %s 的 FieldList 列表中，请检查Domain的配置文件。", key, domain.getFields().getName()));
				return "";
			}
			filedName.append(key).append(",");
			DataType dt = field.getDataType();
			Object obj = domain.get(key);
			if ((dt == DataType.INT) || (dt == DataType.BIGINT) || (dt == DataType.DECIMAL) || (dt == DataType.DOUBLE) || (dt == DataType.FLOAT) || (dt == DataType.SMALLINT) || (dt == DataType.TINYINT))
				filedVale.append(obj).append(",");
			else if (((dt == DataType.DATE) || (dt == DataType.DATETIME)) && ((null == obj) || Strings.isNullOrEmpty(obj.toString())))
				filedVale.append("null").append(",");
			else
				filedVale.append("'").append(obj).append("'").append(",");
		}
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ");
		sql.append(storeName);
		sql.append("(");
		sql.append(StringUtils.removeEnd(filedName.toString(), ","));
		sql.append(")VALUES(");
		sql.append(StringUtils.removeEnd(filedVale.toString(), ","));
		sql.append(")");
		return sql.toString().replace("  ", " ");
	}

	@Override
	public String toUpdateText(String storeName, Domain domain)
	{
		domain.setModifedTime(Dates.now());
		StringBuilder sql = this.toUpdateBaseText(storeName, domain);
		sql.append(" WHERE id = '").append(domain.getId()).append("'");
		return sql.toString().replace("  ", " ");
	}

	@Override
	public String toUpdateText(String storeName, Domain domain, Where subText)
	{
		domain.setModifedTime(Dates.now());
		StringBuilder sql = this.toUpdateBaseText(storeName, domain);
		sql.append(" WHERE ").append(subText.toSql(domain.getFields()));
		return sql.toString().replace("  ", " ");
	}

	@Override
	public String toDeleteText(Fields fields, Id modifiorId, Where where)
	{
		StringBuilder sql = this.toDeleteBaseText(fields, modifiorId);
		if (null != where)
			sql.append(" WHERE ").append(where.toSql(fields));
		return sql.toString().replace("  ", " ");
	}

	@Override
	public String toDeleteText(String storeName, Domain domain)
	{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(storeName);
		sql.append(String.format(" SET isDeleted = 1, modifior = '%s'", domain.getModifior()));
		sql.append(" WHERE id ='").append(domain.getId()).append("'");
		return sql.toString().replace("  ", " ");
	}

	@Override
	public String toRemoveText(Domain domain)
	{
		return this.toRemoveText(domain.getFields().getName(), domain.getId());
	}

	@Override
	public String toRemoveText(String storeName, Id did)
	{
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(storeName);
		sql.append(" WHERE id ='").append(did).append("'");
		return sql.toString();
	}

	@Override
	public String toRemoveText(Fields fields, Id modifiorId, Where where)
	{
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(fields.getName());
		sql.append(" WHERE ");
		sql.append(where.toSql(fields));
		return sql.toString();
	}

	@Override
	public String toExists(String storeName, Domain domain)
	{
		// select Count(*) AS iCount from 登录　where 用户名='zheng'
		Column column = new Column();
		column.append(new Formula("*"));
		column.setFunction(new SqlFunc("count"));
		column.as(RECORD_COUNT);

		Where where = Where.ME();
		for (String key : domain.keySet())
			where = where.and(key, Comparison.Equal, domain.get(key));
		String sql = this.toSelectSqlText(storeName, domain.getFields(), Columns.ME(column), where);
		return sql;
	}

	@Override
	public String toSelectSqlText(String storeName, Fields fields, Columns columns, Where where)
	{
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		String sub = "";
		sb.append(columns.toStringBuilder(fields));
		sb.append(" FROM ");
		sb.append(storeName);
		if (null != where)
		{
			sub = where.toSql(fields);
			CharSequence begin = sub.trim().substring(0, 5).toLowerCase();
			if ("order".equals(begin) || "group".equals(begin) || "limit".equals(begin))
				sb.append(" ").append(where.toSql(fields));
			else
			{
				sb.append(" WHERE ");
				sb.append(where.toSql(fields));
			}
		}
		return sb.toString().replace("  ", " ");
	}

	@Override
	public String toTopSelectSqlText(String storeName, Fields fields, Columns columns, Where where, Integer top)
	{
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT TOP ");
		sb.append(top).append(" ");
		sb.append(columns.toStringBuilder(fields));
		sb.append(" FROM ");

		sb.append(storeName);
		sb.append(" WHERE ");
		sb.append(where.toSql(fields));
		return sb.toString().replace("  ", " ");
	}

	@Override
	public String toCountSelectSqlText(String storeName, Fields fields, Where where)
	{
		Column field = new Column();
		field.setFunction(new SqlFunc("COUNT"));
		field.append(new Formula("*"));
		field.as("recordCount");

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append(field.toSql(fields));
		sb.append(" FROM ");
		sb.append(storeName);
		if (null != where)
		{
			String sub = where.toSql(fields);
			CharSequence begin = sub.trim().substring(0, 5).toLowerCase();
			if ("order".equals(begin) || "group".equals(begin) || "limit".equals(begin))
				sb.append(" ").append(where.toSql(fields));
			else
			{
				sb.append(" WHERE ");
				sb.append(where.toSql(fields));
			}
		}
		return sb.toString().replace("  ", " ");
	}

	@Override
	public String toPageSelectSqlText(String storeName, Fields fields, Columns columns, Where where, Integer start, Integer limit)
	{
		// SELECT * FROM table LIMIT 5,10;
		StringBuilder sb = new StringBuilder();
		if ((start >= 0) && (limit > 0))
		{
			sb.append(" LIMIT ").append(start).append(",").append(limit);
			String subSql = getSelectSqlText(storeName, fields, columns, where);
			sb.insert(0, subSql);
		}
		return sb.toString().replace("  ", " ");
	}

	protected static String getSelectSqlText(final String tableName, final Fields columns, final Columns fields, final Where where)
	{
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append(fields.toStringBuilder(columns));
		sb.append(" FROM ");
		sb.append(tableName);
		if (null != where)
		{
			String sub = where.toSql(columns);
			CharSequence begin = sub.trim().substring(0, 5).toLowerCase();
			if ("order".equals(begin) || "group".equals(begin) || "limit".equals(begin))
				sb.append(" ").append(where.toSql(columns));
			else
			{
				sb.append(" WHERE ");
				sb.append(where.toSql(columns));
			}
		}
		return sb.toString().replace("  ", " ");// SELECT * FROM operationflow WHERE ORDER BY getTicketTime DESC LIMIT 0,20
	}

	/**
	 * 添加数据库字段的Sql语句
	 */
	public String toAddTableFieldText(String tableName, Field field)
	{
		Sber sber = Sber.ME("ALTER TABLE ").append(tableName).append(" ADD ");
		sber.append(field.getName() + ' ');
		sber.append(field.getDataType());
		sber.append('(').append(field.getLength()).append(')').append(' ');
		sber.append(field.getNullEnable());
		return sber.toString();
	}

	protected StringBuilder toUpdateBaseText(String tablename, Domain domain)
	{
		domain.setModifedTime(Dates.now());
		StringBuilder valueFileds = new StringBuilder();
		for (String key : domain.keySet())
		{
			DataType dt = domain.getFields().get(key).getDataType();
			if ((dt == DataType.INT) || (dt == DataType.BIGINT) || (dt == DataType.DECIMAL) || (dt == DataType.DOUBLE) || (dt == DataType.FLOAT) || (dt == DataType.SMALLINT) || (dt == DataType.TINYINT))
				valueFileds.append(key).append("=").append(domain.get(key)).append(",");
			else if (dt == DataType.DATE)
				valueFileds.append(key).append("=").append("'").append(Dates.parse(domain.get(key).toString().substring(0, 19))).append("'").append(",");
			else
			{
				if (domain.get(key) == null || Strings.isNullOrEmpty(domain.get(key).toString()))
					valueFileds.append(key).append("=").append("null").append(",");
				else
				{
					if (dt == DataType.TIMESTAMP)
						valueFileds.append(key).append("=").append("'").append(domain.get(key).toString().substring(0, 19)).append("'").append(",");
					else
						valueFileds.append(key).append("=").append("'").append(domain.get(key)).append("'").append(",");
				}
			}
		}
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(tablename);
		sql.append(" SET ");
		sql.append(valueFileds).deleteCharAt(sql.length() - 1);
		return sql;
	}

	protected StringBuilder toDeleteBaseText(final Fields fields, final Id modifiorId)
	{
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(fields.getName());
		sql.append(String.format(" SET isDeleted = 1, modifior = '%s'", modifiorId.toString()));
		return sql;
	}

	/**
	 * 获得实体所在的表的名称
	 * 
	 * @param domain
	 * @return
	 */
	protected String tableName(Domain domain)
	{
		return domain.getFields().getName();
	}

	// --- ILog -------------------------------------------------

	@Override
	public void logDebug(final Object message)
	{
		LogHelper.logDebug(this, message);
	}

	@Override
	public void logDebug(final Object message, final Throwable t)
	{
		LogHelper.logDebug(this, message, t);
	}

	@Override
	public void logError(final Object message)
	{
		LogHelper.logError(this, message);
	}

	@Override
	public void logError(final Object message, final Throwable t)
	{
		LogHelper.logError(this, message, t);
	}

	@Override
	public void logInfo(final Object message)
	{
		LogHelper.logInfo(this, message);
	}

	@Override
	public void logInfo(final Object message, final Throwable t)
	{
		LogHelper.logInfo(this, message, t);
	}

	@Override
	public void logWarn(final Object message)
	{
		LogHelper.logWarn(this, message);
	}

	@Override
	public void logWarn(final Object message, final Throwable t)
	{
		LogHelper.logWarn(this, message, t);
	}

}
